DROP SEQUENCE BOOK_ID;
DROP TABLE BOOKS;

CREATE SEQUENCE BOOK_ID;
CREATE TABLE BOOKS (
    BOOK_ID INTEGER NOT NULL,
    "CATEGORY" INTEGER NOT NULL,
    TITLE VARCHAR2(40 CHAR) NOT NULL,
    AUTHOR VARCHAR2(40 CHAR) NOT NULL,
    PUB_DATE DATE NOT NULL,
    PRICE NUMBER(12, 2) NOT NULL,
    AMOUNT INTEGER NOT NULL,
    STAR NUMBER(1) DEFAULT 0,
    COVER LONG RAW,
    PRIMARY KEY (BOOK_ID),
    UNIQUE (TITLE)
);

CREATE OR REPLACE PROCEDURE SAVE_BOOK(
    V_RET      OUT SYS_REFCURSOR,
    V_BOOK_ID  IN  INTEGER,
    V_CATEGORY IN INTEGER,
    V_TITLE    IN  VARCHAR2,
    V_AUTHOR   IN  VARCHAR2,
    V_PUB_DATE IN  DATE,
    V_PRICE    IN  NUMBER,
    V_AMOUNT   IN  INTEGER,
    V_STAR IN NUMBER,
    V_COVER IN LONG RAW
) AS
    V_BOOK_ID2 INTEGER;
BEGIN
    IF V_BOOK_ID = 0 THEN
        V_BOOK_ID2 := BOOK_ID.NEXTVAL;
        INSERT INTO BOOKS (BOOK_ID, "CATEGORY", TITLE, AUTHOR, PUB_DATE, PRICE, AMOUNT, STAR, COVER)
        VALUES(V_BOOK_ID2, V_CATEGORY, V_TITLE, V_AUTHOR, V_PUB_DATE, V_PRICE, V_AMOUNT, V_STAR, V_COVER);
    ELSE
        V_BOOK_ID2 := V_BOOK_ID;
        UPDATE BOOKS SET "CATEGORY" = V_CATEGORY, TITLE = V_TITLE, AUTHOR = V_AUTHOR, PUB_DATE = V_PUB_DATE, PRICE = V_PRICE, AMOUNT = V_AMOUNT, STAR = V_STAR, COVER = V_COVER
        WHERE BOOK_ID = V_BOOK_ID2;
    END IF;

    OPEN V_RET FOR
    SELECT BOOK_ID, "CATEGORY", TITLE, AUTHOR, PUB_DATE, PRICE, AMOUNT, STAR, COVER
    FROM BOOKS
    WHERE BOOK_ID = V_BOOK_ID2;
END SAVE_BOOK;
/
